CREATE TABLE YourTableName
(
    dashboardid                   INT,
    workshopname                  VARCHAR(255),
    datetime                      TIMESTAMP,
    scheduledproductionquantity   INT,
    accumulatedproductionquantity INT,
    completionpercentage          INT,
    progressdeviation             INT,
    actualmanpower                INT,
    lineleadername                VARCHAR(255)
);

CREATE TABLE IF NOT EXISTS score
(
    s_id    varchar(20) NOT NULL,
    c_id    varchar(20) NOT NULL,
    s_score int DEFAULT NULL,
    PRIMARY KEY (s_id, c_id)
);

create index score_index on score (s_id, c_id);

CREATE TABLE student
(
    s_id    varchar(32) NOT NULL,
    s_name  varchar(20) NOT NULL DEFAULT '',
    s_birth varchar(20) NOT NULL DEFAULT '',
    s_sex   varchar(10) NOT NULL DEFAULT '',
    PRIMARY KEY (s_id)
);

INSERT INTO student(s_id, s_name, s_birth, s_sex)
VALUES ('01', '赵雷', '1990-01-01', '男'),
       ('02', '钱电', '1990-12-21', '男'),
       ('03', '孙风', '1990-05-20', '男'),
       ('04', '李云', '1990-08-06', '男'),
       ('05', '周梅', '1991-12-01', '女'),
       ('06', '吴兰', '1992-03-01', '女'),
       ('07', '郑竹', '1989-07-01', '女'),
       ('09', '张三', '2017-12-20', '女'),
       ('10', '李四', '2017-12-25', '女'),
       ('11', '李四', '2017-12-30', '女'),
       ('12', '赵六', '2017-01-01', '女'),
       ('13', '孙七', '2018-01-01', '女');

INSERT INTO score (s_id, c_id, s_score)
VALUES ('01', '01', 80),
       ('01', '02', 90),
       ('01', '03', 99),
       ('02', '01', 70),
       ('02', '02', 60),
       ('02', '03', 80),
       ('03', '01', 80),
       ('03', '02', 80),
       ('03', '03', 80),
       ('04', '01', 50),
       ('04', '02', 30),
       ('04', '03', 20),
       ('05', '01', 76),
       ('05', '02', 87),
       ('06', '01', 31),
       ('06', '03', 34),
       ('07', '02', 89),
       ('07', '03', 98);

-- 存储过程 --
do
    $$
    DECLARE
        name TEXT;
BEGIN
    name := 'wudizaba';
raise notice 'hello % ',name;
END
$$;

-- 存储过程
-- $$ :替代单引号 ，简化代码，如果不使用$$，那代码里的字符串都要用“ ”
-- DECLARE  变量定义
-- CONSTANT 常量关键字
--BEGIN 代码入口 END 代码出口
-- RAISE 就是打印语句
-- NOTICE 通知
do
    $$
    DECLARE
        id          INTEGER;
r           NUMERIC(6, 2) DEFAULT 0.0;
name        text;
PI CONSTANT NUMERIC := 3.1415926;
BEGIN
    id = 1;
r = 20;
name = 'wudizaba';

RAISE NOTICE 'id = %',id;
RAISE NOTICE 'r = %',r;
RAISE NOTICE 'name = %',name;
RAISE NOTICE 'PI = %',PI;
END
$$;

-- 存储过程 IF 判断
--  IF  条件 THEN
--  ELSIF 条件 THEN
--  ELSE
--  IF条件语句
DO
    $$

BEGIN
    IF 2 > 3 THEN
            RAISE NOTICE 'wudizaba';
ELSE
            IF 3 < 4 THEN
                RAISE NOTICE 'iiiiiii';
ELSIF 5 = 5 THEN
                RAISE NOTICE 'filelike';
ELSE
                RAISE NOTICE 'dddddddd';
END IF;
END IF;

END
$$;

-- 存储过程 case
--  CASE case_value
-- 	WHEN when_value THEN
-- 		statement_list
-- 	ELSE
-- 		statement_list
-- END CASE;

DO
    $$
    DECLARE
        i INTEGER := 2;
BEGIN
    CASE i
            WHEN 1,2 THEN RAISE NOTICE 'iiiiiii';
WHEN 3,4 THEN raise notice 'ooooooo';
ELSE RAISE NOTICE 'qqqqqqq';
END CASE;

END
$$;

-- 存储过程
-- LOOP 循环
-- 	statement_list
--
-- 	IF exit_condition THEN
-- 		EXIT;
-- 	END IF;
-- END LOOP;
DO
    $$
    DECLARE
        i INTEGER := 1;
BEGIN
    LOOP
            exit when i = 5;
i := i + 1;
CONTINUE WHEN MOD(i, 2);
RAISE NOTICE '第%次徐v你换',i;
END LOOP;

end
$$;


-- 存储过程
-- WHERE 循环
DO
    $$
    DECLARE
        i INTEGER := 1;
BEGIN
    WHILE i < 10
            LOOP
                i := i + 1;
RAISE NOTICE '%',i;
END LOOP;
END
$$;

-- 存储过程
-- -- FOR var IN REVERSE 1..10 by 2 LOOP
-- 	statement_list
-- END LOOP;
DO
    $$
    DECLARE
        arr record;
BEGIN
    FOR arr IN (SELECT * FROM score LIMIT 5)
            LOOP
                RAISE NOTICE '%,%,%',arr.s_id,arr.c_id,arr.s_score;
END LOOP;

END
$$;




-- 游标
-- OPEN 开启游标
-- CLOSE 关闭游标
-- FETCH 获取游标里的数据
DO
    $$
    DECLARE
        emp RECORD;
e1 CURSOR (sss INTEGER) for SELECT *
                            FROM score
                            WHERE s_score = sss ;
BEGIN
    OPEN e1(70);
LOOP
            FETCH e1 INTO emp;
EXIt WHEN NOT FOUND;
RAISE NOTICE '%',emp;
END LOOP;
CLOSE e1;
END
$$;


-- 存储过程
-- 错误信息打印 如果不指定 level，默认为 EXCEPTION，
-- 将会抛出异常并且终止代码运行。
DO
    $$
BEGIN
    RAISE INFO '1';
RAISE DEbug '1';
RAISE WARNING '1';
RAISE log '1';
RAISE NOTICE '1';
-- RAISE EXCEPTION '1';
END
$$;

-- 断言
DO
    $$
    DECLARE
        i int := 1;
BEGIN
    ASSERT 1 = 0;
end
$$;

-- 捕获异常
DO
    $$
    DECLARE
        i int := 1;
BEGIN
    i := 1 / 0;
EXCEPTION
        WHEN division_by_zero THEN
            RAISE NOTICE '除0错误';
WHEN OTHERS then
            RAISE NOTICE '=====';

end
$$;


-- 自定义函数
-- 参数定义 出参 入参
-- RETURNS 返回值类型
-- INTO 查询结果返回
-- LANGUAGE 语言类型
-- SELECT 调用函数
CREATE OR REPLACE FUNCTION countTest(num VARCHAR)
              RETURNS VARCHAR
              AS
              $$
              DECLARE
              nummber INTEGER;
BEGIN
SELECT COUNT(*) INTO nummber FROM student WHERE s_id = num;
RETURN nummber;
END
$$
    LANGUAGE plpgsql;

SELECT countTest('11');

-- 创建存储过程

CREATE OR REPLACE PROCEDURE wudizaba(dashboardid integer, workshopname VARCHAR, datetime timestamp
                                    , scheduledproductionquantity integer
                                    , accumulatedproductionquantity integer, completionpercentage integer, progressdeviation integer, actualmanpower integer
                                    , lineleadername VARCHAR)
              AS
              $$
BEGIN
INSERT INTO YourTableName
VALUES (dashboardid, workshopname, datetime, scheduledproductionquantity, accumulatedproductionquantity,
        completionpercentage, progressdeviation, actualmanpower, lineleadername);
END
$$
    LANGUAGE plpgsql;

CALL wudizaba(2, 'wudizaba', '2028-02-06', 55, 77, 9, 45, 14, 'man what can  i  say');



-- -- 创建函数
CREATE OR REPLACE FUNCTION selectList()
              RETURNS table
          (
              s_id    varchar(20),
              c_id    varchar(20),
              s_score int
              )
              AS
              $$

begin
    return query select *
                 from score
                 where score.s_id in (select distinct s1.s_id
                                      from score s1,
                                           score s2
                                      where s1.s_id = s2.s_id
                                        and s1.c_id = '01'
                                        and s2.c_id = '02'
                                        and s1.s_score > s2.s_score);

end
$$
    language plpgsql;

drop function selectList();
select selectList();


explain
select *
from score
where score.s_id in (select distinct s1.s_id
                     from score s1,
                          score s2
                     where s1.s_id = s2.s_id
                       and s1.c_id = '01'
                       and s2.c_id = '02'
                       and s1.s_score > s2.s_score);

ANALYZE score;

-- 范围分区
CREATE TABLE pkslow_person_r
(
    age  int     not null,
    city varchar not null
) PARTITION BY RANGE (age);

create table pkslow_person_r1 partition of pkslow_person_r for values from (MINVALUE) to (10);
create table pkslow_person_r2 partition of pkslow_person_r for values from (11) to (20);
create table pkslow_person_r3 partition of pkslow_person_r for values from (21) to (30);
create table pkslow_person_r4 partition of pkslow_person_r for values from (31) to (MAXVALUE);

-- list分区
-- 创建主表
create table pkslow_person_l
(
    age  int     not null,
    city varchar not null
) partition by list (city);

-- 创建分区表
CREATE TABLE pkslow_person_l1 PARTITION OF pkslow_person_l FOR VALUES IN ('GZ');
CREATE TABLE pkslow_person_l2 PARTITION OF pkslow_person_l FOR VALUES IN ('BJ');
CREATE TABLE pkslow_person_l3 PARTITION OF pkslow_person_l DEFAULT;

-- 插入测试数据
insert into pkslow_person_l(age, city)
VALUES (1, 'GZ');
insert into pkslow_person_l(age, city)
VALUES (2, 'SZ');
insert into pkslow_person_l(age, city)
VALUES (21, 'SZ');
insert into pkslow_person_l(age, city)
VALUES (13, 'BJ');
insert into pkslow_person_l(age, city)
VALUES (43, 'SH');
insert into pkslow_person_l(age, city)
VALUES (28, 'HK');
insert into pkslow_person_l(age, city)
VALUES (28, 'GZ');

select *
from pkslow_person_l1;

-- 哈希分区
-- 创建主表
create table pkslow_person_h
(
    age  int     not null,
    city varchar not null
) partition by hash (city);

-- 创建分区表
create table pkslow_person_h1 partition of pkslow_person_h for values with (modulus 4, remainder 0);
create table pkslow_person_h2 partition of pkslow_person_h for values with (modulus 4, remainder 1);
create table pkslow_person_h3 partition of pkslow_person_h for values with (modulus 4, remainder 2);
create table pkslow_person_h4 partition of pkslow_person_h for values with (modulus 4, remainder 3);

-- 插入测试数据
insert into pkslow_person_h(age, city)
VALUES (1, 'GZ');
insert into pkslow_person_h(age, city)
VALUES (2, 'SZ');
insert into pkslow_person_h(age, city)
VALUES (21, 'SZ');
insert into pkslow_person_h(age, city)
VALUES (13, 'BJ');
insert into pkslow_person_h(age, city)
VALUES (43, 'SH');
insert into pkslow_person_h(age, city)
VALUES (28, 'HK');

select *
from pkslow_person_h4;


--创建自定义数据类型
create type my_type as
(
    name varchar(10),
    age  smallint
);

-- 创建表测试调用存储过程
create table if not exists stored_procedure_drill
(
    id                serial primary key, -- id 自增
    name              varchar(20) not null,
    age               smallint    not null,
    exercise_duration varchar(10) not null default '一坤年',
    hobbies           varchar(30),
    birth_year        date        not null,
    job               varchar(20),
    purses            integer[],
    very_good_friends my_type,
    create_time       timestamp,
    update_time       timestamp
);

drop table stored_procedure_drill;
comment on column stored_procedure_drill.id IS '主键id';
comment on column stored_procedure_drill.name IS '姓名';
comment on column stored_procedure_drill.age IS '年龄';
comment on column stored_procedure_drill.exercise_duration IS '练习时长';
comment on column stored_procedure_drill.hobbies IS '兴趣爱好';
comment on column stored_procedure_drill.birth_year IS '生日';
comment on column stored_procedure_drill.job IS '工作';
comment on column stored_procedure_drill.purses IS '钱包';
comment on column stored_procedure_drill.very_good_friends IS '好朋友';
comment on column stored_procedure_drill.create_time IS '创建时间';
comment on column stored_procedure_drill.update_time IS '修改时间';




DROP TRIGGER trigger_update_last_updated
    ON  stored_procedure_drill;
-- 创建存储过程-》新增
create or replace procedure
              saveData( name varchar, age smallint, exercise_duration varchar
                          , hobbies varchar, birth_year date, job varchar, purses integer[]
                          , very_good_friends my_type)
                      as
$$
begin
insert into stored_procedure_drill(name, age, exercise_duration, hobbies, birth_year, job, purses, very_good_friends)
values (name, age, exercise_duration, hobbies, birth_year, job, purses, very_good_friends);
end
$$
    language plpgsql;

drop procedure saveData;
drop procedure updateData;
drop procedure deleteData;
drop procedure getData;
-- 调用存储过程
call saveData(cast('1' as varchar), cast(1 as smallint), cast('1' as varchar), cast('1' as varchar)
    , cast('2023-01-02' as date), cast('1' as varchar), cast('{1, 2, 3}' as integer[]),
              (cast('zhangsan' as varchar), cast(18 as smallint))
    );


-- 创建存储过程-》修改
create or replace procedure
              updateData(id1 integer, name1 varchar, age1 smallint, exercise_duration1 varchar
                            , hobbies1 varchar, birth_year1 date, job1 varchar, purses1 integer[]
                            , very_good_friends1 my_type)
                        as
$$
declare
    count_num integer;
begin
select count(*) into count_num from stored_procedure_drill where id = id1;
if count_num < 1 then
insert into stored_procedure_drill(name, age, exercise_duration, hobbies, birth_year, job, purses, very_good_friends)
values (name1, age1, exercise_duration1, hobbies1, birth_year1, job1, purses1, very_good_friends1);
raise notice '没有此数据，默认新增';
else
update stored_procedure_drill
set name              = name1
  , age               =age1
  , exercise_duration = exercise_duration1
  , hobbies           = hobbies1
  , birth_year        = birth_year1
  , job               = job1
  , purses            = purses1
  , very_good_friends = very_good_friends1
where id = id1;
raise notice '修改成功';
end if;
end
$$
    language plpgsql;

call updateData(2, cast('1' as varchar), cast(55 as smallint), cast('1' as varchar), cast('1' as varchar)
    , cast('2023-01-02' as date), cast('1' as varchar), cast('{1, 2, 3}' as integer[]),
                (cast('zhangsan' as varchar), cast(18 as smallint))
    );


-- -- 创建存储过程-》删除
create or replace procedure deleteData(id1 integer)
              as
              $$
              declare
              count_num integer;
begin
select count(*) into count_num from stored_procedure_drill where id = id1;
if count_num < 1 then
        raise exception '没有这条数据，无法删除';
else
delete from stored_procedure_drill where id = id1;
end if;
end
$$
    language plpgsql;

call deleteData(1);

-- 创建函数-》查询
create or replace FUNCTION selectData( page_num integer, page_size integer)
              returns setof stored_procedure_drill
              as
              $$
begin
    return query select id,
                        name,
                        age,
                        exercise_duration,
                        hobbies,
                        birth_year,
                        job,
                        purses,
                        very_good_friends,
                        create_time,
                        update_time
                 from stored_procedure_drill
                 order by update_time desc
                 limit page_size offset (page_num - 1) * page_size;
end
$$
    language plpgsql;
DROP FUNCTION selectdata(integer, integer, integer);

-- 调用函数
select selectData(null, null, null);
select selectData(1,10);

-- 创建存储过程-》查询
create or replace procedure getData(page_num integer, page_size integer,out arr REFCURSOR)
              as
              $$
begin
    open arr for   select id,
                          name,
                          age,
                          exercise_duration,
                          hobbies,
                          birth_year,
                          job,
                          purses,
                          very_good_friends,
                          create_time,
                          update_time
                   from stored_procedure_drill
                   order by update_time desc
                   limit page_size offset (page_num - 1) * page_size;
end
$$
    language plpgsql;



-- 调用存储过程
do $$
    DECLARE
        output_val  REFCURSOR;
em stored_procedure_drill%rowtype;
cursor_exists boolean := false;
begin
call getData(cast(null as integer), cast(null as integer), output_val);
cursor_exists := FOUND; -- Check if cursor exists
loop
            fetch output_val into em;
exit when em is null;
raise notice '%,%,%,%,%,%,%,%,%,%,%',em.id,em.name,em.age,em.exercise_duration,em.hobbies,em.birth_year,em.job,em.purses,em.very_good_friends,em.create_time,em.update_time;
end loop;
close output_val;
end
    $$
language plpgsql;

-- 创建函数调用查询数据的存储过程
create FUNCTION getSelectPro(page_num integer, page_size integer)
    returns  refcursor
as $$
    declare
        output_val  REFCURSOR;
em stored_procedure_drill%rowtype;
cursor_exists boolean := false;
arr varchar[];
a1 varchar;
begin
call getData(cast(page_num as integer), cast(page_size as integer), output_val);
cursor_exists := FOUND; -- Check if cursor exists
loop
            fetch output_val into em;
exit when em is null;
raise notice '%,%,%,%,%,%,%,%,%,%,%',cast(em.id as integer) ,cast(em.name as varchar),cast(em.age as smallint),cast(em.exercise_duration as varchar),cast(em.hobbies as varchar),cast(em.birth_year as date)
                ,cast(em.job as varchar),cast(em.purses as integer[]),cast(em.very_good_friends as my_type),cast(em.create_time as timestamp),cast(em.update_time as timestamp);

a1 :=  array_to_string(ARRAY(SELECT unnest(ARRAY[(cast(em.id as integer) ,cast(em.name as varchar),cast(em.age as smallint),cast(em.exercise_duration as varchar),cast(em.hobbies as varchar),cast(em.birth_year as date)
                ,cast(em.job as varchar),cast(em.purses as integer[]),cast(em.very_good_friends as my_type),cast(em.create_time as timestamp),cast(em.update_time as timestamp))])), ',') AS formatted_record;
arr := array_append(arr,a1);
end loop;
close output_val;
return arr;
end
$$
    language plpgsql;

drop FUNCTION getSelectPro();
-- 调用函数
select getSelectPro(1,10);
-- {"(12,test,18,两年半,唱、跳、RAP、篮球,1990-11-20,无业,\"{1,2,3,4,5,6,7,8,9,10}\",\"(妮基肽酶,20)\",\"2024-05-05 09:45:09.035562\",\"2024-05-05 09:45:09.035562\")","(11,test,18,两年半,唱、跳、RAP、篮球,2008-12-02,无业,\"{1,2,3,4,5,6,7,8,9,10}\",\"(妮基肽酶,20)\",\"2024-05-04 23:26:53.094757\",\"2024-05-04 23:26:53.094757\")","(8,发放,0,阿萨法发,发生发,2024-04-02,萨法,\"{100,200,233,662}\",\"(guozhiwen,0)\",\"2024-05-04 04:13:31.851788\",\"2024-05-04 23:26:49.389214\")","(10,test,18,两年半,唱、跳、RAP、篮球,1969-04-20,无业,\"{1,2,3,4,5,6,7,8,9,10}\",\"(妮基肽酶,20)\",\"2024-05-04 15:55:45.081356\",\"2024-05-04 15:55:45.081356\")","(3,1,1,1,1,2024-01-01,1,\"{1,2,3}\",\"(2,18)\",\"2024-05-04 04:04:46.217191\",\"2024-05-04 09:38:12.882297\")","(4,1,1,1,1,2024-01-01,1,\"{1,2,3}\",\"(2,18)\",\"2024-05-04 04:04:46.217191\",\"2024-05-04 09:38:12.882297\")","(7,1,1,1,1,2024-01-01,1,\"{1,2,3}\",\"(2,18)\",\"2024-05-04 04:13:31.066924\",\"2024-05-04 04:13:31.066924\")","(1,1,1,1,1,2024-01-01,1,\"{1,2,3}\",\"(2,18)\",\"2024-05-04 04:13:11.149997\",\"2024-05-04 04:13:11.149997\")","(5,1,1,1,1,2024-01-01,1,\"{1,2,3}\",\"(2,18)\",\"2024-05-04 04:04:46.217191\",\"2024-05-04 04:04:46.217191\")"}`
-- 创建存储过程统计
create or replace procedure getCount(c1 out INTEGER)
              as
              $$
begin
select count(*) into c1
from stored_procedure_drill ;
end
$$
    language plpgsql;

create FUNCTION getCountPro()
    returns  integer
    as $$
    DECLARE
        c integer;
begin
call getCount(c);
return c;
end
$$
language plpgsql;

select getCountPro();
-- 触发器
-- 创建数据变更记录表
create table if not exists data_change_log_sheet_for_stored_procedure_drill

(
    id   serial  primary key,
    name              varchar(20) not null,
    age               smallint    not null,
    exercise_duration varchar(10) not null default '一坤年',
    hobbies           varchar(30),
    birth_year        date        not null,
    job               varchar(20),
    purses            integer[],
    very_good_friends my_type,
    operation_time       timestamp, -- 操作时间
    operation_type varchar(10)  -- 操作类型
);

-- 使用触发器实现时间自动更新
-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_last_updated_column()
              RETURNS TRIGGER AS
              $$
BEGIN
    if tg_op = 'INSERT' then
        NEW.create_time = NOW();
NEW.update_time = NOW();
elsif TG_OP = 'UPDATE' then
        NEW.update_time = NOW();
end if;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
CREATE TRIGGER trigger_update_last_updated
    BEFORE INSERT OR UPDATE
    ON stored_procedure_drill
    FOR EACH ROW
EXECUTE FUNCTION update_last_updated_column();

-- 创建触发器函数
CREATE OR REPLACE FUNCTION recording_data_logs()
              RETURNS TRIGGER AS
              $$
BEGIN
    IF TG_OP = 'INSERT' THEN
INSERT INTO data_change_log_sheet_for_stored_procedure_drill(name, age, exercise_duration, hobbies, birth_year, job, purses, very_good_friends, operation_time, operation_type)
VALUES (new.name,new.age, new.exercise_duration, new.hobbies, new.birth_year, new.job, new.purses, new.very_good_friends, now(), 'INSERT');
ELSIF TG_OP = 'UPDATE' THEN
INSERT INTO data_change_log_sheet_for_stored_procedure_drill(name, age, exercise_duration, hobbies, birth_year, job, purses, very_good_friends, operation_time, operation_type)
VALUES (old.name,old.age, old.exercise_duration, old.hobbies, old.birth_year, old.job, old.purses, old.very_good_friends, now(), 'UPDATE');
END IF;
RETURN NEW;
END
$$ LANGUAGE plpgsql;


-- 创建触发器函数删除
CREATE OR REPLACE FUNCTION recording_data_logs_delete()
              RETURNS TRIGGER AS
              $$
BEGIN
    IF TG_OP = 'DELETE' THEN
INSERT INTO data_change_log_sheet_for_stored_procedure_drill(name, age, exercise_duration, hobbies, birth_year, job, purses, very_good_friends, operation_time, operation_type)
VALUES (old.name,old.age, old.exercise_duration, old.hobbies, old.birth_year, old.job, old.purses, old.very_good_friends, now(), 'DELETE');
END IF;
RETURN NEW;
END
$$ LANGUAGE plpgsql;

-- 触发器  删除
CREATE TRIGGER trigger_recording_data_logs_delete
    AFTER DELETE
    ON stored_procedure_drill
    FOR EACH ROW
    EXECUTE FUNCTION recording_data_logs_delete();

-- 创建触发器
CREATE TRIGGER trigger_recording_data_logs
    BEFORE INSERT OR UPDATE
    ON stored_procedure_drill
    FOR EACH ROW
EXECUTE FUNCTION recording_data_logs();


insert into stored_procedure_drill(name, age, exercise_duration, hobbies, birth_year, job, purses, very_good_friends)
values ('1',1,'1','1','2024-01-01','1','{1, 2, 3}',('2',18));

update stored_procedure_drill set name              = 'name1'
                                , age               =188
                                , exercise_duration = '1'
                                , hobbies           = 'hobbies1'
                                , birth_year        = '2024-01-01'
                                , job               = 'job1'
                                , purses            = '{1, 2, 3}'
                                , very_good_friends = ('2',18)
where id = 1;


delete from stored_procedure_drill where id = 9;




